from sys import maxsize

import orjson
from datetime import datetime
from fastapi.encoders import jsonable_encoder
from sqlalchemy.ext.associationproxy import association_proxy
from .common import *
from sqlalchemy import Integer, DECIMAL
from sqlalchemy import SmallInteger, Boolean, CHAR, Column, Computed, DECIMAL, Date, DateTime, Float, ForeignKeyConstraint, Identity, Index, Integer, LargeBinary, Numeric, PrimaryKeyConstraint, SmallInteger, String, Table, Unicode, text
from sqlalchemy.dialects.mssql import DATETIME2, IMAGE, TINYINT


@add_serializer
class TemplateType(YgBase):
    __tablename__: str = "YG_TemplateType"
    __table_args__ = {'extend_existing': True}
    id: Mapped[int_pk_auto]
    code: Mapped[required_unique_name]
    name: Mapped[required_unique_name]
    category: Mapped[required_string]
    templates: Mapped[List["Template"]] = relationship(back_populates="template_type")

    def __repr__(self) -> str:
        return f'id: {self.id}, name: {self.name}'
    def __str__(self) -> str:
        return f'id: {self.id}, name: {self.name}'


@add_serializer
class Template(YgBase):
    __tablename__: str = "YG_Template"
    __table_args__ = {'extend_existing': True}
    id: Mapped[int_pk_auto]
    name: Mapped[required_unique_name]
    description: Mapped[required_string] = mapped_column(String(1000), default='', nullable=True)
    config: Mapped[str] = mapped_column(String(4000), nullable=False)
    template_type_id: Mapped[int] = mapped_column(ForeignKey('YG_TemplateType.id'))
    template_type: Mapped[TemplateType] = relationship(lazy=False, back_populates="templates")

    def __repr__(self) -> str:
        return f'id: {self.id}, name: {self.name}'
    def __str__(self) -> str:
        return f'id: {self.id}, name: {self.name}'


# 定义关联中间表，并有金额字段
@add_serializer
class CheckingBillFee(SqlBase):
    __tablename__: str = "YG_CheckingBill_fee"
    id_CheckingBill = mapped_column(Integer,ForeignKey('YG_CheckingBill.id'), primary_key=True)
    id_FeeType = mapped_column(Integer,ForeignKey('YG_FeeType.id'), primary_key=True)
    amount = mapped_column(DECIMAL(12, 2))
    checkingBill = relationship('CheckingBill', back_populates='checkingBillFees')
    yGFeeType = relationship('YGFeeType', back_populates='checkingBillFees')


""" 这种方式;中间表如果有数据获取比较困难;新增数据也困难-->改用代理表模式
CheckingBillFee = Table('YG_CheckingBill_fee', SqlBase.metadata,
 Column('id_CheckingBill', Integer, ForeignKey('YG_CheckingBill.id')),
    Column('id_FeeType', Integer, ForeignKey('YG_FeeType.id')),
    Column('amount', DECIMAL(12, 2))  # 新增的分数字段
)
"""

@add_serializer
class CheckingBill(YgBase):
    __tablename__: str = "YG_CheckingBill"
    id: Mapped[int_pk_auto]
    bill_date: Mapped[timestamp_default_now]
    bill_no: Mapped[str] = mapped_column(String(50))
    bill_type: Mapped[int]
    flight_no: Mapped[str] = mapped_column(String(50))
    departure_port: Mapped[str] = mapped_column(String(10))
    destination_port: Mapped[str] = mapped_column(String(10))
    status: Mapped[int] = mapped_column(SmallInteger,default=0, nullable=True)
    articles: Mapped[int]
    weight: Mapped[DECIMAL(20, 2)] = mapped_column(DECIMAL(20, 2), nullable=True)
    weight_for_fee: Mapped[DECIMAL(20, 2)] = mapped_column(DECIMAL(20, 2), nullable=True)
    unit_price: Mapped[DECIMAL(12, 2)] = mapped_column(DECIMAL(12, 2), nullable=True)
    memo: Mapped[str]= mapped_column(Unicode(4000))
    total_amount: Mapped[DECIMAL(20, 2)] = mapped_column(DECIMAL(20, 2), nullable=True)
    id_trade_partner: Mapped[int] = mapped_column(ForeignKey('YG_TradePartner.id'), nullable=True)
    id_company: Mapped[str]
    id_accounted: Mapped[int]
    id_invoiced: Mapped[int]
    checkingBillFees: Mapped[List['CheckingBillFee']]  = relationship('CheckingBillFee', back_populates='checkingBill')
    yGFeeTypes: Mapped[List['YGFeeType']] = association_proxy('checkingBillFees', 'yGFeeType',creator=lambda yGFeeType: CheckingBillFee(yGFeeType=yGFeeType))
    # yGFeeTypes = relationship('YGFeeType', secondary=CheckingBillFee, back_populates='checkingBills') # old way

    def __repr__(self) -> str:
        return f'id: {self.id}, name: {self.bill_no}, departure_port: {self.departure_port}'
    def __str__(self) -> str:
        return f'id: {self.id}, name: {self.bill_no}, departure_port: {self.departure_port}'


@add_serializer
class YGFeeType(YgBase):
    __tablename__ = 'YG_FeeType'
    id: Mapped[int_pk_auto]
    name = mapped_column(Unicode(100))
    code = mapped_column(String(50))
    checkingBillFees = relationship('CheckingBillFee', lazy=False, back_populates='yGFeeType')
    # checkingBills = association_proxy('checkingBillFees', 'checkingBill')
    amount = association_proxy('checkingBillFees', 'amount')
    # checkingBills = relationship('CheckingBill', secondary=CheckingBillFee, back_populates='yGFeeTypes') # old way

    def __repr__(self) -> str:
        return f'id: {self.id}, name: {self.name}'
    def __str__(self) -> str:
        return f'id: {self.id}, name: {self.name}'


@add_serializer
class CheckingBillFromExcel(YgBase):
    __tablename__: str = "YG_CheckingBillFromExcel"
    bill_no: Mapped[int_pk_str]
    bill_date: Mapped[timestamp_default_now]
    id_CheckingBill: Mapped[int] = mapped_column(ForeignKey('YG_CheckingBill.id'),nullable=True)
    id_trade_partner: Mapped[int] = mapped_column(ForeignKey('YG_TradePartner.id'), nullable=True)
    file_name: Mapped[str]=mapped_column(String(64))
    weight_for_fee: Mapped[DECIMAL(20, 2)] = mapped_column(DECIMAL(20, 2), nullable=True)
    total_amount: Mapped[DECIMAL(20, 2)] = mapped_column(DECIMAL(20, 2), nullable=True)

    def __repr__(self) -> str:
        return f'name: {self.bill_no}'
    def __str__(self) -> str:
        return f'name: {self.bill_no}'


@add_serializer
class Client(YgBase):
    __tablename__ = 'YG_client'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='PK__YG_clien__3213E83FFEB3F042'),
    )
    id: Mapped[int_pk_auto]
    phone: Mapped[str] = mapped_column(String(20))
    email: Mapped[str] = mapped_column(String(50))
    addr: Mapped[str] = mapped_column(Unicode(100))
    memo: Mapped[str] = mapped_column(Unicode(4000))
    name: Mapped[str] = mapped_column(Unicode(100))
    profit: Mapped[str] = mapped_column(Numeric(20, 2))
    paid_amount: Mapped[str] = mapped_column(Numeric(20, 2))
    total: Mapped[int] = mapped_column(Integer)
    unpaid_amount = mapped_column(Numeric(21, 2), Computed('(isnull([profit],(0))-isnull([paid_amount],(0)))', persisted=False))
    income = mapped_column(Numeric(20, 2))
    expenditure = mapped_column(Numeric(20, 2))
    invoice_amount = mapped_column(DECIMAL(12, 2))

    def __repr__(self) -> str:
        return f'name: {self.name}'
    def __str__(self) -> str:
        return f'name: {self.name}'


@add_serializer
class BillCheckedRecord(YgBase):
    __tablename__ = 'YG_billChecked_record'
    id : Mapped[int_pk_auto]
    income = mapped_column(Numeric(20, 2))
    expenditure = mapped_column(Numeric(20, 2))
    profit = mapped_column(Numeric(20, 2))
    memo: Mapped[str] = mapped_column(Unicode(4000))
    id_client: Mapped[int] = mapped_column(ForeignKey('YG_client.id'), nullable=False)
    id_trade_partner: Mapped[int] = mapped_column(ForeignKey('YG_TradePartner.id'), nullable=False)
    accounted_date = mapped_column(DATETIME2)
    id_company = mapped_column(Unicode(32))
    total = mapped_column(Integer)
    id_checked_bills = mapped_column(String(4000))

    def __repr__(self) -> str:
        return f'name: {self.memo}'
    def __str__(self) -> str:
        return f'name: {self.memo}'


@add_serializer
class Invoice(YgBase):
    __tablename__ = 'YG_invoice'
    id : Mapped[int_pk_auto]
    income = mapped_column(Numeric(20, 2))
    expenditure = mapped_column(Numeric(20, 2))
    profit = mapped_column(Numeric(20, 2))
    invoice_amount = mapped_column(Numeric(20, 2))
    invoice_no = mapped_column(String(50))
    memo: Mapped[str] = mapped_column(Unicode(4000))
    id_client: Mapped[int] = mapped_column(ForeignKey('YG_client.id'), nullable=True)
    invoice_date = mapped_column(DATETIME2)
    id_company = mapped_column(Unicode(32))
    total = mapped_column(Integer)
    id_invoiced_bills = mapped_column(String(4000))
    id_payment = mapped_column(ForeignKey('YG_payment.id'), nullable=True)

    def __repr__(self) -> str:
        return f'name: {self.memo}'
    def __str__(self) -> str:
        return f'name: {self.memo}'


@add_serializer
class Payment(YgBase):
    __tablename__ = 'YG_payment'
    id : Mapped[int_pk_auto]
    id_client: Mapped[int] = mapped_column(ForeignKey('YG_client.id'), nullable=True)
    amount = mapped_column(Numeric(20, 2))
    memo = mapped_column(Unicode(4000))
    payment_date = mapped_column(DATETIME2)
    bill_month = mapped_column(String(10))
    invoice_nos = mapped_column(String(1000))
    def __repr__(self) -> str:
        return f'name: {self.memo}'
    def __str__(self) -> str:
        return f'name: {self.memo}'


@add_serializer
class sys_depart(YgBase):
    __tablename__: str = "sys_depart"
    id: Mapped[str] = mapped_column(Unicode(32), primary_key=True, nullable=False)
    parent_id:Mapped[str] = mapped_column(Unicode(32), ForeignKey('sys_depart.id'), nullable=True)
    children: Mapped[list['sys_depart']] = relationship('sys_depart', back_populates='parent', remote_side=[id])
    parent: Mapped['sys_depart'] = relationship('sys_depart', back_populates='children', remote_side=[parent_id])
    depart_name: Mapped[str] = mapped_column( Unicode(100), nullable=False)
    depart_name_en: Mapped[str] = mapped_column(Unicode(500), nullable=False)
    depart_name_abbr: Mapped[str] = mapped_column(Unicode(500), nullable=False)
    depart_order: Mapped[int] = mapped_column(Integer)
    description: Mapped[str] = mapped_column(Unicode)
    org_category: Mapped[str] = mapped_column(Unicode(10))
    org_type: Mapped[str] = mapped_column(Unicode(10))
    org_code: Mapped[str] = mapped_column(Unicode(64))
    mobile: Mapped[str] = mapped_column(Unicode(32))
    fax: Mapped[str] = mapped_column(Unicode(32))
    address: Mapped[str] = mapped_column(Unicode(100))
    memo: Mapped[str] = mapped_column(Unicode(500))
    status: Mapped[str] = mapped_column(Unicode(1), nullable=False)
    depart_class: Mapped[str] = mapped_column(Unicode(1000))

    def __repr__(self) -> str:
        return f'name: {self.depart_name}'
    def __str__(self) -> str:
        return f'name: {self.depart_name}'


@add_serializer
class TradePartner(YgBase):
    __tablename__: str = "YG_TradePartner"
    id: Mapped[int_pk_auto]
    group_type: Mapped[str]
    fee_items: Mapped[str] = mapped_column(String(64))
    bill_type: Mapped[int] = mapped_column(String(64))
    id_client: Mapped[int] = mapped_column(ForeignKey('YG_client.id'), nullable=True)
    id_company: Mapped[int] = mapped_column(ForeignKey('sys_depart.id'), nullable=True)

    def __repr__(self) -> str:
        return f'name: {self.group_type}'
    def __str__(self) -> str:
        return f'name: {self.group_type}'


@add_serializer
class CityName(YgBase):
    __tablename__: str = "YG_CityName"
    id: Mapped[int_pk_auto]
    country: Mapped[str] = mapped_column(String(100))
    city: Mapped[str] = mapped_column(String(100))
    country_code: Mapped[str] = mapped_column(String(20))
    city_code: Mapped[str] = mapped_column(String(20))
    country_shortname: Mapped[str] = mapped_column(String(10))
    country_english_name: Mapped[str] = mapped_column(String(50))
    city_fullname: Mapped[str] = mapped_column(String(100))
    city_shortname: Mapped[str] = mapped_column(String(10))

    def __repr__(self) -> str:
        return f'name: {self.city_fullname}'

    def __str__(self) -> str:
        return f'name: {self.city_fullname}'


